What is CRUD?

Create:

insert new records to the database

READ:

Select or Search for records in the database

Update:

Change some fields of the record in database

Delete:

Remove records from the database

Install SQLC

brew install sqlc

SQLC init

sqlc init

Edit sqlc.yaml

• You need to create folder : sqlc migration query

• emit_json_tags : we want sqlc to add JSON tags to the generated structs

• Go 每日一库之 sqlc

• Configuration

  version: 1
  packages:
      - path: './db/sqlc'
        name: 'db'
        engine: 'postgresql'
        schema: './db/migration'
        queries: './db/query'
  			emit_json_tags: true
  			emit_prepared_queries: false
        emit_interface: false
  			emit_exact_table_names: false
  

  • emit_prepared_queries
    • Defaults to false
    • If true, include support for prepared queries.
    • It can optimize the database's performance.
  • emit_interface
    • Defaults to false
    • If true, output a Querier
       interface in the generated package (querier.go)
    • It will be useful in a mock database.
  • emit_exact_table_names
    • Defaults to false
    • If true, struct names will mirror table names.
      • accounts table → Accounts struct
      • var account Accounts (might be confused as multiple objects)

SQLC

• db.go

  // Code generated by sqlc. DO NOT EDIT.
  // versions:
  //   sqlc v1.17.2
  
  package db
  
  import (
  	"context"
  	"database/sql"
  )
  
  type DBTX interface {
  	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
  	PrepareContext(context.Context, string) (*sql.Stmt, error)
  	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
  	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
  }
  
  func New(db DBTX) *Queries {
  	return &Queries{db: db}
  }
  
  type Queries struct {
  	db DBTX
  }
  
  func (q *Queries) WithTx(tx *sql.Tx) *Queries {
  	return &Queries{
  		db: tx,
  	}
  }
  

  • DB Query Flow

  db, _ := sql.Open("driver", "dsn")
  queries := New(db)
  account, err := queries.CreateAccount(ctx, params)
  

  

  • DB Transaction Flow

  db, _ := sql.Open("driver", "dsn")
  queries := New(db)
  tx, _ := db.Begin()
  queriesWithTx := queries.WithTx(tx)
  account, err := queriesWithTx.CreateAccount(ctx, params)
  tx.Commit() // 或 tx.Rollback()
  

  

Edit Makefile

sqlc:
					sqlc generate

Generate CRUD from SQLC

Account Table

Create

• :one Return one object
• RETURNING * : return all record columns

db/query/account.sql

-- name: CreateAccount :one
INSERT INTO accounts
  (
  owner,
  balance,
  currency
  )
VALUES
  (
    $1, $2, $3
)
RETURNING *;

func (q *Queries) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
	// 使用 q.db.QueryRowContext 方法執行 SQL 查詢，傳入 SQL 命令和參數
	row := q.db.QueryRowContext(ctx, createAccount, arg.Owner, arg.Balance, arg.Currency)
	
	// 創建一個 Account 類型的變量 i，用於存放查詢結果
	var i Account
	
	// 使用 row.Scan 方法將查詢結果映射到 i 的相應字段
	// 通過傳入指針，row.Scan 會直接修改 i 的值
	err := row.Scan(
		&i.ID,        // 將查詢結果的 ID 存放到 i.ID
		&i.Owner,     // 將查詢結果的 Owner 存放到 i.Owner
		&i.Balance,   // 將查詢結果的 Balance 存放到 i.Balance
		&i.Currency,  // 將查詢結果的 Currency 存放到 i.Currency
		&i.CreatedAt, // 將查詢結果的 CreatedAt 存放到 i.CreatedAt
	)

	// 返回 Account 類型的 i 和可能的錯誤 err
	return i, err
}

1. Generate Code

make sqlc

sqlc generate

1. Code files:
   1. models.go : struct definition of models (Account、Entry、Transfer)
   2. db.go : contain dbtx interface
   3. account.sql.go : account.sql's Query Functions

tree db/sqlc
db/sqlc
├── account.sql.go
├── db.go
└── models.go

Read

• GetAccount : Only get one account
• ListAccounts : Get all accounts
• OFFSET : 從LIMIT+1 開始回傳幾筆Data(用於分頁)

db/query/account.sql

- name: GetAccount :one
SELECT *
FROM accounts
WHERE id = $1
LIMIT 1;

-- name: ListAccounts :many
SELECT *
FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2;

Update

• :one Return one object
• RETURNING * : return all record columns

db/query/account.sql

-- name: UpdateAccount :one
UPDATE accounts SET balance = $2
WHERE id = $1
RETURNING *;

Delete

db/query/account.sql

-- name: DeleteAccount :exec
DELETE FROM accounts WHERE id = $1;

Homework:

• 完成Entry、Transfer table 的CRUD

  • Entry

    /*
    one: returns a single record
    many: returns multiple record
    RETURNING * : return all record columns
    */
    -- name: CreateEntry :one
    INSERT INTO entries
      (
      account_id,
      amount
      )
    VALUES
      (
        $1, $2
    )
    RETURNING *;
    
    -- name: GetEntry :one
    SELECT *
    FROM entries
    WHERE id = $1
    LIMIT 1;
    
    /*
    GetEntries returns all entries for a given account
    */
    -- name: ListEntries :many
    SELECT *
    FROM entries
    WHERE account_id = $1
    ORDER BY id
    LIMIT $2
    OFFSET
    $3;
    

  • Transfer

    /*
    one: returns a single record
    many: returns multiple record
    RETURNING * : return all record columns
    */
    
    -- name: CreateTransfer :one
    INSERT INTO transfers
      (
      from_account_id,
      to_account_id,
      amount
      )
    VALUES
      (
        $1, $2, $3
      )
    RETURNING *;
    
    -- name: GetTransfer :one
    SELECT *
    FROM transfers
    WHERE id = $1
    LIMIT 1;
    
    -- name: ListTransfers :many
    SELECT *
    FROM transfers
    WHERE from_account_id = $1 OR to_account_id = $2
    ORDER BY id
    LIMIT $3
    OFFSET
    $4;